insert overwrite table jms_dwd.dwd_order_source_update_sync_dt partition (dt = '{{ execution_date | cst_ds }}')
select
    der.billcode as billcode
   ,way.subscribe_source_code as ordersource_code
   ,way.subscribe_source_name as ordersource_name
   ,der.input_time
from (
    select *
    from jms_dwd.dwd_order_source_detail_dt --取两天前未订阅的数据
    where dt >= date_add('{{ execution_date | cst_ds }}', -2)
      and dt < '{{ execution_date | cst_ds }}'
      and subscribe_point = '0'
) der
inner join (
    select *
    from (
        select
              waybill_no
             ,subscribe_source_code
             ,subscribe_source_name
             ,row_number() over(partition by waybill_no order by last_update_time asc) as rn
        from jms_ods.yl_oms_oms_waybill --运单表
        where dt = '{{ execution_date | cst_ds }}' 
    ) a where a.rn = 1 
) way on der.billcode = way.waybill_no
distribute by 1;